﻿namespace jzgl.Controller
{
    //using Microsoft.SqlServer.Management.Common;
    //using Microsoft.SqlServer.Management.Smo;
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Windows.Forms;
    using MySql.Data.MySqlClient;

    public class ConnDBMysql
    {
        private MySqlConnection conn = null;
        private int num = 0;

        public void Back_DataBase(string savefilePath)
        {
            using (MySqlConnection connection = this.GetConnString())
            {

                connection.Open();
                string str = connection.Database.ToString();
                //ServerConnection serverConnection = new ServerConnection(connection);
                //Server srv = new Server(serverConnection);
                //Console.WriteLine(srv.InstanceName.ToString());
                //BackupDeviceItem item = new BackupDeviceItem(savefilePath, DeviceType.File);
                //Backup backup = new Backup();
                //backup.Devices.Add(item);
                //backup.Action = BackupActionType.Database;
                //backup.BackupSetDescription = "SQL Express is a great product!";
                //backup.BackupSetName = "SampleBackupSet";
                //backup.Database = str;
                //backup.ExpirationDate = new DateTime(0x7d7, 5, 1);
                //backup.LogTruncation = BackupTruncateLogType.Truncate;
                //backup.SqlBackup(srv);
                Console.WriteLine("Your backup is complete.");
            }
        }

        public void comboxDataBind(ComboBox cbox, string sqlstr, string dvf, string dtext)
        {
            this.conn.Open();
            DataTable table = new DataTable();
            DataSet dataSet = new DataSet();
            MySqlCommand selectCommand = new MySqlCommand(sqlstr, this.conn);
            new MySqlDataAdapter(selectCommand).Fill(dataSet);
            table = dataSet.Tables[0];
            cbox.DataSource = table;
            cbox.DisplayMember = dtext;
            cbox.ValueMember = dvf;
            this.conn.Close();
        }

        public int Count_Record(string sql)
        {
            int num2;
            this.conn = this.GetConnString();
            this.conn.Open();
            int num = 0;
            MySqlCommand command = new MySqlCommand(sql, this.conn);
            MySqlDataReader reader = command.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    num = int.Parse(reader[0].ToString());
                }
                num2 = num;
            }
            catch (Exception)
            {
                num2 = num;
            }
            finally
            {
                command.Dispose();
                this.conn.Close();
            }
            return num2;
        }

        public void dgvDataBind(DataGridView dgv, string sqlstr)
        {
            this.conn = this.GetConnString();
            this.conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter(sqlstr, this.conn);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            dgv.DataSource = dataSet;
            this.conn.Close();
        }

        public static DataSet ExcelToDS(string Path)
        {
            DataSet set2;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=NO;'";
            OleDbConnection connection = new OleDbConnection(connectionString);
            try
            {
                connection.Open();
                string selectCommandText = "";
                OleDbDataAdapter adapter = null;
                DataSet dataSet = null;
                selectCommandText = "select * from [sheet1$]";
                adapter = new OleDbDataAdapter(selectCommandText, connectionString);
                dataSet = new DataSet();
                adapter.Fill(dataSet, "table1");
                connection.Close();
                set2 = dataSet;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                connection.Close();
            }
            return set2;
        }

        public static DataSet ExcelToDS(string Path, string filename)
        {
            DataSet dataSet = null;
            DataSet set2;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=NO;'";
            OleDbConnection connection = new OleDbConnection(connectionString);
            try
            {
                connection.Open();
                string selectCommandText = "";
                OleDbDataAdapter adapter = null;
                selectCommandText = "select * from [sheet1$] ";
                adapter = new OleDbDataAdapter(selectCommandText, connectionString);
                dataSet = new DataSet();
                adapter.Fill(dataSet, "table1");
                connection.Close();
                set2 = dataSet;
            }
            catch (Exception)
            {
                dataSet = null;
                set2 = dataSet;
            }
            finally
            {
                connection.Close();
            }
            return set2;
        }

        public static DataSet ExcelToDSBy_FileName(string Path, string filename)
        {
            DataSet dataSet = null;
            DataSet set2;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=NO;;IMEX=1'";
            OleDbConnection connection = new OleDbConnection(connectionString);
            try
            {
                connection.Open();
                string selectCommandText = "";
                OleDbDataAdapter adapter = null;
                selectCommandText = "select * from [Sheet1$] ";
                adapter = new OleDbDataAdapter(selectCommandText, connectionString);
                dataSet = new DataSet();
                adapter.Fill(dataSet, "table1");
                connection.Close();
                set2 = dataSet;
            }
            catch (Exception)
            {
                dataSet = null;
                set2 = dataSet;
            }
            finally
            {
                connection.Close();
            }
            return set2;
        }

        public DataTable excuteQuery(string strSQL)
        {
            DataTable dataTable = null;
            MySqlConnection connString = this.GetConnString();
            try
            {
                dataTable = new DataTable();
                connString.Open();
                new MySqlDataAdapter(strSQL, connString).Fill(dataTable);
                connString.Close();
            }
            catch (Exception)
            {
                dataTable = null;
            }
            finally
            {
                if (connString != null)
                {
                    connString.Close();
                }
            }
            return dataTable;
        }

        public int excuteUpdate(string strSQL)
        {
            int num = 0;
            MySqlConnection connString = this.GetConnString();
            try
            {
                connString.Open();
                num = new MySqlCommand(strSQL, connString).ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message + "22222222222");
                num = -1;
            }
            finally
            {
                if (connString != null)
                {
                    connString.Close();
                }
            }
            return num;
        }

        public int Execute_Sql(string sql)
        {
            int num;
            try
            {
                this.conn = this.GetConnString();
                this.conn.Open();
                this.num = new MySqlCommand(sql, this.conn).ExecuteNonQuery();
                num = this.num;
            }
            catch (Exception)
            {
                num = this.num;
            }
            finally
            {
                this.conn.Close();
            }
            return num;
        }

        public MySqlDataReader ExecuteReader(string sql)
        {
            MySqlDataReader reader2;
            MySqlConnection connString = this.GetConnString();
            MySqlCommand command = new MySqlCommand(sql, this.conn);
            try
            {
                connString.Open();
                reader2 = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                connString.Close();
            }
            return reader2;
        }

        public DataSet FillDataSet(string sql)
        {
            DataSet set2;
            DataSet dataSet = new DataSet();
            DataTable table = new DataTable();
            MySqlConnection connString = this.GetConnString();
            try
            {
                connString.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, connString);
                new MySqlDataAdapter(selectCommand).Fill(dataSet);
                connString.Close();
                set2 = dataSet;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                connString.Close();
            }
            return set2;
        }

        public DataTable FillDataTable(string sql)
        {
            DataTable table2;
            DataSet dataSet = new DataSet();
            DataTable table = new DataTable();
            MySqlConnection connString = this.GetConnString();
            try
            {
                connString.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, connString);
                new MySqlDataAdapter(selectCommand).Fill(dataSet);
                table = dataSet.Tables[0];
                connString.Close();
                table2 = table;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                connString.Close();
            }
            return table2;
        }

        public MySqlConnection GetConnString()
        {
            try
            {
                string myconn = "Database='test';Data Source=localhost;User ID=root;Password=123456;CharSet=utf8;";

                MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
                builder.Server = "localhost";
                builder.Database = "test";
                builder.CharacterSet = "gb2312";
                builder.Port = 3306;

                //builder.ConnectionProtocol
                //builder.DataSource = @".\sqlexpress";
                //builder.AttachDBFilename = AppDomain.CurrentDomain.BaseDirectory.ToString() + @"data\jzgldata.mdf";
                //Console.WriteLine(builder.AttachDBFilename.ToString());
                //builder.IntegratedSecurity = true;
                //builder.UserInstance = true;
                builder.UserID = "sa";
                builder.Password = "123";
                this.conn = new MySqlConnection(builder.ConnectionString);
            }
            catch (Exception)
            {
                throw;
            }
            return this.conn;
        }

        public int GetScalar(string sql, params MySqlParameter[] values)
        {
            int num = 0;
            MySqlConnection connString = this.GetConnString();
            try
            {
                connString.Open();
                MySqlCommand command = new MySqlCommand(sql, connString);
                command.Parameters.AddRange(values);
                num = Convert.ToInt32(command.ExecuteNonQuery());
            }
            catch (Exception exception)
            {
                num = -1;
                Console.WriteLine(exception.Message + "333333333");
            }
            finally
            {
                if (connString != null)
                {
                    connString.Close();
                }
            }
            return num;
        }

        public int Record_Count(string sql)
        {
            int num2;
            this.conn = this.GetConnString();
            this.conn.Open();
            int num = 0;
            MySqlCommand command = new MySqlCommand(sql, this.conn);
            try
            {
                num = (int) command.ExecuteScalar();
                num2 = num;
            }
            catch (Exception)
            {
                num2 = num;
            }
            finally
            {
                command.Dispose();
                this.conn.Close();
            }
            return num2;
        }

        public void RestoreBase(string openfilePath)
        {
            try
            {
                string str = "";
                using (MySqlConnection connection = this.GetConnString())
                {
                    //connection.Open();
                    //str = connection.Database.ToString();
                    //connection.ChangeDatabase("master");
                    //ServerConnection serverConnection = new ServerConnection(connection);
                    //Server srv = new Server(serverConnection);
                    //Console.WriteLine(srv.InstanceName.ToString());
                    //BackupDeviceItem item = new BackupDeviceItem(openfilePath, DeviceType.File);
                    //Restore restore = new Restore();
                    //restore.Devices.Add(item);
                    //restore.NoRecovery = false;
                    //restore.ReplaceDatabase = true;
                    //restore.Database = str;
                    //restore.SqlRestore(srv);
                    Console.WriteLine("My database has been restored.");
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message + "00000000000异常00000000");
            }
        }
    }
}

